﻿SET NOCOUNT ON;

--Add calculated geography field to ProjectSiteTable
ALTER TABLE dbo.ProjectSiteSet ADD Location AS geography::Point(Latitude, Longitude, 4326); -- SRID 4326 = Bing Map

--Basic data
INSERT INTO OrganizationType (Type)
VALUES ('Informal group'),
       ('Non-governmental organization'),
       ('Religious entity'),
       ('University or school'),
       ('Governmental entity');

INSERT INTO ProjectSectorSet (Name)
VALUES ('Agriculture'),
       ('Education'),
       ('Health'),
       ('Peace and security'),
       ('Environment'),
       ('Housing'),
       ('Water, sanitation and hygiene'),
       ('Human rights, democracy and governance'),
       ('Economic development');

INSERT INTO OrganizationProjectTypeSet (Name)
VALUES ('Funding'),
       ('Implementing'),
       ('Funding and implementing');

SET IDENTITY_INSERT OrganizationSet ON;

INSERT INTO OrganizationSet (Id, Name, Description, Website, OrganizationTypeID)
VALUES ('1', 'Hope Eden', 'Hope Eden was founded in 1997 it was a heartfelt desire of Shiekar Kumbur to build up a primary school as an answer for the needs of the children of the Hope Eden community as well as a support to other mothers from the neighbourhood working on the farm.', 'http://www.hope-eden.org/', '2'),
       ('2', 'GeekGive', 'A volunteer coding event to do good!', 'http://www.softability.fi', 1);

SET IDENTITY_INSERT OrganizationSet OFF;

SET IDENTITY_INSERT ProjectSet ON;

INSERT INTO ProjectSet(Id, Name, Description, ProjectSectorId, BeneficiaryNumber, BeneficiaryDescription, ShortTermVolunteering, LongTermVolunteering)
VALUES ('1', 'Community School', 'Four times a week the kids learn to play with each other, exercise themselves in hygiene, extend their farming knowledge and practise cooking and baking at an open fire place or with a cooker', '2', '40', 'Local children', 'True', 'True'),
       ('2', 'Village clinic', 'A group of volunteers host a village clinic once a week to attend the needs of under 5 year old children in Kobi village', '3', '500', 'children under 5 and their mothers', 'True', 'False'),
       ('3', 'Community Farm', 'Teach farming skills and have a livelihood', '1', '15', 'Local community', 'True', 'True'),
       ('4', 'Orphanage Maitama', 'Assist and support a city orphanage', '2', '40', 'children aged 0 to 11', 'True', 'False');

SET IDENTITY_INSERT ProjectSet OFF;

INSERT INTO OrganizationProjectSet (OrganizationId, ProjectId, OrganizationProjectTypeId)
VALUES ('1', '1', '2'),
       ('1', '3', '2');

INSERT INTO UserProjectTypeSet (Name)
VALUES ('Staff'), ('Volunteer'), ('Follower');

INSERT INTO OrganizationRoleUserTypeSet (Name)
VALUES ('Staff'), ('Volunteer'), ('Follower');

INSERT INTO ProjectActivitySet (Description, ProjectId)
SELECT 'Weekly doctors consultation for children', Id
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectActivitySet (Description, ProjectId)
SELECT 'Weekly nutritional teaching for mothers', Id
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectActivitySet (Description, ProjectId)
SELECT 'Daily classes', Id
FROM ProjectSet
WHERE Name = 'Community School'

INSERT INTO ProjectObjectiveSet (Description, ProjectId)
SELECT 'Reduce child mortality', Id
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectObjectiveSet (Description, ProjectId)
SELECT 'Improve literacy rates', Id
FROM ProjectSet
WHERE Name = 'Community School'

INSERT INTO ProjectResultSet (Description, ProjectId)
SELECT 'A list of all families and their children produced', Id
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectResultSet (Description, ProjectId)
SELECT 'All childrens birth dates recorded', Id
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectObjectiveSet (Description, ProjectId)
SELECT 'Improve food security', Id
FROM ProjectSet
WHERE Name = 'Community Farm'

INSERT INTO ProjectActivitySet (Description, ProjectId)
SELECT 'Research the most productive crops in the soil conditions', Id
FROM ProjectSet
WHERE Name = 'Community Farm'

INSERT INTO ProjectActivitySet (Description, ProjectId)
SELECT 'Organize teaching and awareness raising', Id
FROM ProjectSet
WHERE Name = 'Community Farm'

INSERT INTO ProjectSiteSet (ProjectId, Latitude, Longitude)
SELECT Id, '9.034375165168948', '7.53387451171875'
FROM ProjectSet
WHERE Name = 'Village Clinic'

INSERT INTO ProjectSiteSet (ProjectId, Latitude, Longitude)
SELECT Id, '8.881001205714659', '7.23724365234375'
FROM ProjectSet
WHERE Name = 'Community School'

INSERT INTO ProjectSiteSet (ProjectId, Latitude, Longitude)
SELECT Id, '9.07615702957916', '7.499633431434631'
FROM ProjectSet
WHERE Name = 'Orphanage Maitama'

INSERT INTO ProjectSiteSet (ProjectId, Latitude, Longitude)
SELECT Id, '8.881001205714659', '7.23724365234375'
FROM ProjectSet
WHERE Name = 'Community Farm'
